﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using ANZ_AIM_DAL;
using ANZ_AIM_BLL.Model;
using ANZ_AIM_BLL.Utils;
using ANZ_AIM_FRAMEWORK;


namespace ANZ_AIM_BLL
{
    public class CustomReportBLL
    {
        private string ConnectionString;
        private StringBuilder command;

        public CustomReportBLL()
        {
            ConnectionString = DBHelper.GetMainMdbConnectionString();
        }

        public DataTable Search(string saleCode, string fromDateWithSpace, string toDateWithSpace
                                , string fromDateNoSpace, string toDateNoSpace
                                , Dictionary<string, string> TablesAndColumnsForQuery
                                , List<string> DisplayColumns, List<string> GroupColumns, List<string> GroupColumnsDisplay)
        {
            try
            {
                DataSet dsResult;
                command = new StringBuilder();
                command.AppendFormat("SELECT {0} ", string.Join(",", GroupColumnsDisplay.ToArray()));
                command.AppendFormat(",{0}", string.Join(",", DisplayColumns.ToArray()));

                command.Append(" FROM (");
                int count = 0;
                foreach (var item in TablesAndColumnsForQuery)
                {
                    count++;
                    //if (item.Key.Equals("SaleDIS"))
                    //{
                    //    GroupColumns[2] = "Format(ActionRequiredDate,\"yyyy \\MMM\") as [Month]";
                    //}
                    //if (item.Key.Equals("AgentLOC"))
                    //{
                    //    GroupColumns[0] = "CurrentSalesCode as [SaleCode]";
                    //}
                    command.AppendFormat("SELECT {0} ", string.Join(",", GroupColumns.ToArray()));
                    command.AppendFormat(" ,{0} FROM {1} ", item.Value, item.Key);
                    //command.AppendFormat(" SELECT {0} FROM {1} ", item.Value, item.Key);
                    if (!count.Equals(TablesAndColumnsForQuery.Count))
                    {
                        command.Append(" UNION ");
                    }

                    //if (item.Key.Equals("SaleDIS"))
                    //{
                    //    GroupColumns[2] = "[Month]";
                    //}
                    //if (item.Key.Equals("AgentLOC"))
                    //{
                    //    GroupColumns[0] = "[SaleCode]";
                    //}
                }
                command.Append(") ");
                //command.Append(" WHERE (@saleCode IS NULL OR SaleCode Like @saleCode)");
                command.Append(" WHERE ([Month] BETWEEN @FromDateWithSpace AND @ToDateWithSpace OR [Month] BETWEEN @FromDateNoSpace AND @ToDateNoSpace)");
                command.AppendFormat(" GROUP BY {0}", string.Join(",", GroupColumns.ToArray()));

                

                //13Jan2015 BEGIN Add for search Sale Code by Personal Code
                if (!saleCode.Equals(""))
                {
                    StringBuilder command1 = new StringBuilder();
                    command1.Append("SELECT * FROM (");
                    command1.Append(command.ToString());
                    //command1.Append(" ) WHERE [Personal Code] IN ( ");
                    command1.Append(" ) WHERE [Profile ID] IN ( ");
                    //command1.Append("SELECT [Personal Code] FROM ( ");
                    command1.Append("SELECT [Profile ID] FROM ( ");
                    command1.Append(command.ToString());
                    //command1.Append(" ) WHERE [Sale Code] = @saleCode AND [Personal Code] <> ''");
                    command1.Append(" ) WHERE [Sale Code] = @saleCode AND [Profile ID] <> ''");
                    command1.Append(") OR [Sale Code] = @saleCode");

                    OleDbParameter[] parameters = new OleDbParameter[5];
                    parameters[0] = new OleDbParameter("@FromDateWithSpace", fromDateWithSpace);
                    parameters[1] = new OleDbParameter("@ToDateWithSpace", toDateWithSpace);
                    parameters[2] = new OleDbParameter("@FromDateNoSpace", fromDateNoSpace);
                    parameters[3] = new OleDbParameter("@ToDateNoSpace", toDateNoSpace);
                    if (string.IsNullOrEmpty(saleCode))
                    {
                        parameters[4] = new OleDbParameter("@saleCode", DBNull.Value);
                    }
                    else
                    {
                        parameters[4] = new OleDbParameter("@saleCode", saleCode);
                    }
                    dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command1.ToString(), parameters);
                }
                else
                {
                    OleDbParameter[] parameters = new OleDbParameter[4];

                    parameters[0] = new OleDbParameter("@FromDateWithSpace", fromDateWithSpace);
                    parameters[1] = new OleDbParameter("@ToDateWithSpace", toDateWithSpace);
                    parameters[2] = new OleDbParameter("@FromDateNoSpace", fromDateNoSpace);
                    parameters[3] = new OleDbParameter("@ToDateNoSpace", toDateNoSpace);

                    dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command.ToString(), parameters);
                }
                //13Jan2015 END Add for search Sale Code by Personal Code 

                //DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, "SELECT [SaleCode],[PersonalCode],[Month] ,MAX(BatchFileSaleInfoID) as BatchFileSaleInfoID1 FROM ( SELECT SaleCode, PersonalCode, REPLACE(Format(ActionRequiredDate,\"yyyy \\MMM\"), \" \", \"\") AS Month, BatchFileSaleInfoID FROM SaleDIS  UNION  SELECT SaleCode, PersonalCode, REPLACE([Month], \" \",\"\"), BatchFileSaleInfoID FROM SalePIP )  WHERE  Month BETWEEN \"2013M01\" AND \"2015M01\" GROUP BY [SaleCode],[PersonalCode],[Month]");
                //DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, "SELECT [SaleCode],[PersonalCode],MAX(BatchFileSaleInfoID) as BatchFileSaleInfoID1 FROM ( SELECT SaleCode, PersonalCode, BatchFileSaleInfoID FROM SaleDIS  UNION  SELECT SaleCode, PersonalCode, BatchFileSaleInfoID FROM SalePIP )  GROUP BY [SaleCode],[PersonalCode]");


                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }
    }
}
